package com.sanmina.route.action;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.sanmina.route.entity.WiMap;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by mercy on 5/17/16.
 */
@WebServlet("/angularServlet")
public class AngularGridAction extends HttpServlet {

	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;

	public synchronized static Connection getConnection() {
		String url = "jdbc:postgresql://127.0.0.1:5432/mesportal";
		String user = "postgres";
		String pass = "admin";
		Connection conn = null;
		try {
			Class.forName("org.postgresql.Driver");
			conn = DriverManager.getConnection(url, user, pass);
		} catch (ClassNotFoundException | SQLException e) {
			e.printStackTrace();
		}

		return conn;
	}

	@Override
	protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		List<WiMap> list = new ArrayList<WiMap>();
		String sql = "SELECT * FROM wi_map";
		PreparedStatement pstat = null;
		ResultSet rs = null;
		String name = req.getParameter("name");
		System.out.println(name+"************8");
		try {
			Connection conn = getConnection();
			pstat = conn.prepareStatement(sql);
			rs = pstat.executeQuery();
			while (rs.next()) {
				WiMap map = new WiMap();
				map.setPartFamily(rs.getString(7));
				map.setPartKey(rs.getInt(8));
				map.setProcessName(rs.getString(9));
				map.setAttaFileName(rs.getString(19));
				map.setContentType(rs.getString(4));
				map.setStartPage(rs.getInt(11));
				map.setEndPage(rs.getInt(12));
				map.setDocNumber(rs.getString(15));
				map.setRevision(rs.getString(17));
				map.setAssignTo(rs.getString(23));
				map.setAssignValue(rs.getString(24));
				map.setEffectiveFrom(rs.getString(21));
				
				map.setEffectiveTo(rs.getString(22));
				list.add(map);
			}
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (pstat != null) {
					pstat.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		JSONArray ja = JSONArray.fromObject(list);
		JSONObject jo = new JSONObject();
		jo.put("list", ja);
		jo.put("counts", list.size());
		resp.setCharacterEncoding("UTF-8");
		resp.setHeader("Cache-Control", "no-cache");
		resp.setContentType("text/html;charset=UTF-8");
		resp.getWriter().print(jo);
		resp.getWriter().close();
	}

	@Override
	protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		this.doGet(req, resp);
	}
}
